Contact Tracing System for Epidemiologists and Epidemiological Research


















Aman Batra

NUID: 001877232
Class: CS5200
Academic Term: Summer-2, 2020
Instructor: Dr. Martin Schedlbauer
Email : Batra.am@northeastern.edu
Type : Individual Submission
GITHUB Repository: https://github.com/predictbay/amancs5200







Section 1 : Problem Definition - Contact Tracing


1.1 What is Contact Tracing?

According to WHO: Contact tracing is the process of identifying, assessing, and managing people who have been exposed to a disease to prevent onward transmission.

1.2 What is the purpose of Contact Tracing?

When systematically applied, contact tracing will break the chains of transmission of COVID-19 to prevent future waves or surges of cases, and to enable us to get back to work in a much safer way. Contact Tracing is an essential public health tool for controlling the virus.

1.3 How does Contact Tracing work?


Contact tracing for COVID-19 requires identifying people who may have been exposed to COVID-19 and following them up daily for 14 days from the last point of exposure.

The goal is to create a spider web of corona-virus transmission

Multiple Approaches To Trace Contacts:

  1. An application approach, Continuous subject monitoring and data gathering is achieved using a mobile-application. Patients use the application to self-assess symptoms and report their interactions with other contacts, which can then be notified via the app, and put in incubation. For Example, a portable contact tracing application with real-time threat notifications based on GPS location/Bluetooth tracking, daily self-assessments, and contact reporting.

  2. A general approach, where infected person contacts local Public Health Authorities and notifies them, PHAs then takes report of the person via calls & interviews, and prescribes test or medical assessments. Daily reports and health updates are taken by the assigned personnel until the incubation period is active. All the contract tracing is done manually by the PHAs, who then feed the data into a central reporting system.

  3. A combined approach, where both self-assessments and manual assessments are possible. Patients choose if they would like to contact and schedule meetings with PHAs or prefer the use of a real-time contact tracing app regularly. PHAs gather and anonymise all the data, and put it in a graph database. The graph databases are then analysed to create heatmaps of the COVID-19 affected areas, which are later turned into containment zones.


Section - 2 Most Efficient Approach - Contact Tracing(Combined Approach)

Combined approach assumes that the local, regional, or national Public Health body of a geographical area, has offered three ways, to keep a check on COVID-19 epidemic: a mobile application, a website, and a dedicated COVID-19 helpline to implement contact tracing measures.

1.Automated:

A self-assessment and self-reporting based portable application, capable of running on multiple types of devices such as mobile phones, tablets, and laptops with real-time geolocation and Bluetooth based proximity tracing of other users, running the same application of their devices. Each user’s HealthStatus tokens are broadcasted within a 30 metres radius, and every user of the application in the proximity range will receive these tokens. If a sick user is nearby, all the users in the vicinity will get a threat alert. The total interaction time or visit time of a user will be recorded whenever he or she, meets or passes by, a sick person, or visits a place. If a user is not feeling and suspects that he or she might have been exposed to COVID-19, then there is a self-assessment option which contains a predefined set of questions, that can predict the likelihood of infection. If the assessment score bypasses the defined threshold, all users in the vicinity are notified of potential threat; Local health authority(LHA) is notified via the application. The LHA then prescribes a suitable COVID-19, and if the user tests positive, an incubation period of 14 days is initiated, which contains daily self-assessments, self-quarantining, and self-reporting of every place visited, every person in touch, and every notable interaction made within the past 14 days.

2.Semi-Automated:

Some users have privacy issues when it comes to using applications that continuously record user data, and keep surveillance over their activities. For such users, a website or a similar app without monitoring is a better option, to implement contact tracing. When such a user feels sick, he can go to the LHA website, and take a self-assessment test, if the test results indicate potential infection, their identity is anonymised and location data is fed into the central database(common to all strategies). The user has to take a daily self-assessment until he or she is marked ‘healthy’ again, and all the contact reporting is done on the website. The central database is used to create heatmaps of coronavirus stricken areas, the website users can manually check the heatmap zones online, whereas it is inbuilt in the application.

3.Manual:

For users, who have no viable means to use the website or application, there is a dedicated helpline number, which takes care of daily assessments, and contact reporting. Things are done manually, via phone conversations or administered meetings, and all the gathered data is then manually uploaded to the database.



Section 3. Database Tools Specification


Overview


The database for a Contact Tracing system is can be implemented using many relational and non-relational DBMS such MySQL, PostgreSQL, MongoDB, Neo4j, Oracle DB. Parts of the project can be implemented using graph-databases, because they will be best suited for running depth queries and discovering links at greater depths, but for this particular Practicum, we will be using MySQL as a DBMS tool ubiquitously, and R Studio for running Analytics. MySQL has many advantages and some limitations, as given below:
## Advantages of MySQL

  1. Open source, inexpensive and readily available.

  2. Industry Standard, and very popular.

  3. Extensive support available online.

  4. Ease, Intuitiveness and Usability

  5. Outstanding InnoDB engine.

Drawbacks

  1. Scalability issues can arise with time.

  2. Not very easy to debug.

  3. Does not support very large databases efficiently.


Advantages of R

  1. Open source, Platform Independent

  2. Rapid, and quality plotting

  3. Non-Coder friendly, anyone can start plotting within a hours

  4. Rich and continuously growing sets of packages (>10000) in the CRAN repository

Drawbacks

  1. R Utilizes more memory as objects are stored in the memory.

  2. Slower than other programming languages like Python and Matlab

  3. Does not support very large scale applications efficiently.


Section 4. Overall Assumptions & Constraints.

Assumptions:

  1. Only some parts of the whole application will be reflected in the relational database, some data will stored locally on the host devices, such as state variables, local variables, events data, device permissions details, etc.

  2. There can be many use cases, but the database is designed keeping in mind only some of these use cases, hence some parts of the database can be missing For Example. The assessment-survey module can have 4 more classes, but we are only using one for now.

  3. The depth of queries will be set to 5, because MySQL is not a graph-database, it takes a good amount of time and processing power to create Joins, and make connections.

  4. Application logic and host application will be created at a later time.



Constraints:


Domain Level:

  1. Varchar for string data

  2. Integer for whole numbers

  3. Boolean for binary choices (True or False, Yes or No, Correct or Incorrect, these kind of choices will be implemented using 0s & 1s

  4. Enum for Lists or categorical attributes

  5. Text for descriptions


Referential Integrity:

  1. Place extends to Interactions, AppUser, Visits, PublicHealthAuthority

  2. Person extends to AppUser and PublicHealthWorker

  3. AppUser extends to Place, Interactions, Assessment, HealthReportCheck and Person

  4. UserEvents extends to Visits, Interactions and ContactHistoryLog

  5. PersonNotification, AppUserEmail, PersonPhone are linking tables.


Entity Integrity:

  1. All classes have primary keys, all primary keys are set to NOT NULL

  2. Key constraints are enabled SET FOREIGN_KEY_CHECKS = 1

Section 5. UML Class Diagram



Class Definitions

Lists all the classes (Including Hidden Ones, that are a part of relational Schema)

  1. Place - Place is any valid location on planet earth with proper coordinates, streetarea, city, state, country and Zip attributes. In my model place is referred to, by many other classes AppUser, PublicHealthAuthority, Interactions and Visits. Because each interaction take place at place, each visit also has a place. similarly. each naive AppUser has an address linked to them.

  2. Person - Person refers to any user who exists in the database. Person is a parent class to AppUser and PublicHealthWorker, each person compulsorily has a first name and, phone number also is mandatory, such as in case we want to notify a person who came contact with another person, we will need a phone number.

  3. PersonPhone - Each Person can have multiple phone numbers, so PersonPhone acts as a one to many relationship table with PhoneNo as primary key because, each phone number uniquely identifies a person.

  4. AppUser - Any person who uses the contact tracing application falls under the AppUser category, an AppUser needs an email ID to register, hence email Id is a mandatory field. AppUser also have an addressID linked to them, this address is taken during the initial registration of contact tracing application

  5. AppUserEmail - A linking table to list all the email addresses of every app user.

  6. PublicHealthWorker - A person who works for a public health authority, PublicHealthWorker assess every Health Report and Assessment Survey. Every survey and health report is assigned to a PublicHealthWorker, He analyses the report/survey and collects a ContactHistoryLog from the suspected/infected AppUser, and informs the PublicHealthAuthority about any person who came in contact with this AppUser, and all the places that this infected AppUser had visited.

  7. PublicHealthAuthority - It's a public healthcare governing body, that defines and controls all the public health protocols, provides necessary facilities such as Emergency services, hospital appoints, covid-19 tests to the people who fall under their jurisdiction. Every PublicHealthAuthority has an address linked to them via place class, and many helpline numbers which they provide for public service.

  8. PublicHealthAuthorityHelpline - This entity is a linking table which contains helpline number of every public health authority

  9. Notifications - The PublicHealthWorker informs the respective PublicHealthAuthority about all the interactions of a covid patient or suspect. Then this PublicHealthAuthority releases a notification to communicate the possibility of an exposure event to every contact of this patient. A notification includes details such as exposure event, who infected you, where and when, along with a textual description which contains all the relevant communication and call-back details.

  10. PersonNotification - A many-to-many linking table which links each unique notification to a unique person.

  11. UserEvent : A superclass for Visits and Interactions, Each userevent can include a minimum of one person and a maximum of two persons, In case the event was a visit to a hotspot area, then the event will contain userid of visiting person and place id of the place, in case event was a interaction between two person then the userevent will contain ids of both the participating person and placeid of the place where this interaction took place.

  12. Interactions : - A recursive relationship between AppUser and Person(AppUser or PublicHealthWorker), Interactions take place between any two persons at particular place, on a particular date, in a particular time window. The datetime details of interaction start and end are recorded, and can be used later for mining data precisely and efficiently. AppUser has gps enabled in the phone to placeid of interaction is captured automatically via application logic. But it can be manually entered if needed.

  13. Visits : Each time a person visits a place away from home, and spends some time, the application logic captures the details of the visit, such as the placeid, total time of visit, etc. Each visit indicates a unique person visiting a unique place, for a particular datetime window.

  14. ContactHistoryLog : A log is created for every AppUser and assigned to a PublicHealthWorker when this AppUser is found suspected or infected. The log lists out all interactions and visits made by this AppUser. On these event, the PublicHealthWorker can use application logic to extract information such as known contacts, frequent contacts, places visited and other such details. The PublicHealthWorker then passes these details to PublicHealthAuthority which then takes the required actions

  15. Assessment : A form a survey to check the probability of COVID-19 infection in an AppUser, a survey can be taken by self, by doctors, or by others. The assessment contains a questionnaire which is out of the scope of this project. It also contains a response sheet as a blog or large text. We have included the response sheet, possible survey outcomes if COVID is suspected or not. A PublicHealthWorker oversees the assessment, if an AppUser is marked as CovidSuspected.

  16. HealthReportCheck : A health report card generated for an AppUser, after he reported himself as unwell in the app and got all the test and medical checkups done, the health-card lists details such Temperature, OxygenLevel, Test status of Covid test and other relevant details such as symptoms and prescriptions in the description.


Assumptions:


  • The database is designed to incorporate the “Hybrid Approach” as described in the approaches document. So the relational system can accept both manual and app-generated values.

  • The Contact Tracing definition here assumes, tracing down the people who came in contact with an infected person, or notifying people who could have been exposed during an interaction, or at a place where an infected user was present at the same time (if a person is aware or not of his condition at the time of interaction/visit is out of context, we list out all the interaction not just before the test report came out positive, after that also)

  • Every Place has a latitude and a longitude, as pinpoint location is really important in tracking and comes handy in automated contract tracing also. In case an address is missing lat, long, application logic will populate it, not the fields are set NOT NULL.

  • Each Interaction , AppUser and Visit can have only one place linked to them at a time, we are not moving interactions and visits, Also multiple addresses for a person are not allowed in out model

  • Here AppUser and PublicHealthWorker inherit attributes from Person, but there can be other subclasses also, it is out of the scope of this project.

  • Each PublicHealthWorker can work at one PublicHealthAuthority at a time.

  • Every Assessment and HealthReportCheck have exactly one PublicHealthWorker linked to them, who is responsible for reporting it to the PHA.

  • An AppUser can take multiple assessments and get multiple health reports. The derived attributes of AppUser are not stored in the database, the application logic will take care of that.

  • A PublicHealthWorker can be assigned to take many ContactHistoryLog , but every log has exactly one worker listed on it.

  • A UserEvent can either be a visit or an interaction, not both, as per our assumption, an interaction will always require a second person. Also the UserEvent not always have to be auto-generated by app, it can be manually added by asking the related person for details, in case if the user is not using this app.

  • An AppUser can have multiple email ids and phone numbers, similarly a public authority can have multiple helpline numbers.





The Conceptual Model


Design Tool Used: Visual Paradigm Community Edition for Mac

Link to Conceptual Model Files: https://github.com/predictbay/amancs5200


“Conceptual Model”



Section 6. Entity Relationship Diagram



##Link to LucidChart “Click Here To Open”



Logical Model


Section 7. Schema Generation & Normalization Check Results



## Relational Schema:


Place(placeint ,placename,streetarea,city,state,zip,latitude,longitude)

Person(PersonID,firstName,lastName)

PersonPhone(PhoneNo,PersonID)

AppUser(UserID,AddressID,RecoveredFromCovid)

AppUserEmail(EmailID, UserID)

PublicHealthWorker(WorkerID,title,officeID ,PublicAuthID)

PublicHealthAuthority(AuthorityID ,AuthAddressID,Name,Jurisdiction)

PublicHealthAuthorityHelpline(HelplineNo,AuthorityID)

UserEvent(EventID,EventStartTime,EventEndTime,Description)

Visits(VisitID,VisitingUserID,VisitPlaceID)

Interactions(InteractionID ,interactingUserID,VisitorID,InteractionPlaceID)

Notifications(Nid ,PHAAuthorityID,timestamp,OtherInformation)

HealthReportCheck(ReportID ,UserID,Temperature,OxygenLevel,HealthStatus,TestResult,Descriptions,CheckingWorkerID, ReportDate)

Assessment(AssessID,TakerID,CovidSuspected,TimeStamp,ResponseSheet, AssesseeType, OverseeingWorkerID)

PersonNotification(Nid, PersonID)




Normalization to BCNF


This following table lists out every relation in the database and provides proof to make sure its in BCNF. There Is no need to prove lower normal forms like 1NF, 2NF and 3NF because, if a relationship in BCNF, it IMPLIES that it is already normalized in lower forms.

The relationships shows below, comply with all of the following criterion, needed for validating BCNF.

1. Every relationship has a valid candidate key as their determinants( All determinants are candidate keys)

2. There is no partial dependency of any kind

3. No composite candidate keys with overlapping attributes

4. No multivalued attributes exist

5. No transitive dependency.

“BCNF CHECK” “BCNF CHECK”

SCHEMA TABLES

AppUser


Table Structure:

“AppUser”


Functional Dependency Check

UserID=>addressid

UserID=>name

UserID=>birthdate


  • UserID is a candidate key and Primary key which uniquely identifies Birthdate, AddressID, RecoveredFromCovid.

  • AddressID is a Candidate Key, because it can uniquely identify an AppUser.

  • No Partial Dependency or Multivalued Attribute exist in this relationship.

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF.


AppUserEmail

Table Structure:


“AppUserEmail”


EmailID -> UserId


  • UserID is a candidate key and Primary key which uniquely identifies Birthdate, AddressID, RecoveredFromCovid.

  • AddressID is a Candidate Key, because it can uniquely identify an AppUser

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


Person

Table Structure:

“Person”


Functional Dependency Check


PersonID=>firstname

PersonID=>lastname


  • PersonID is a candidate key and Primary key which uniquely identifies firstname, lastname

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


PersonPhone


Table Structure:

“PersonPhone”

Functional Dependency Check


PhoneNo=>PhoneID


  • PhoneNo is a candidate key and Primary key which uniquely identifies PersonID

  • PersonID is not unique here because a user can have multiple phone numbers.

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


PublicHealthAuthority


Table Structure:

Functional Dependency Check


AuthorityID=>AuthAddressID

AuthorityID=>Name

AuthorityID=>Jurisdiction


  • authorityid is a candidate key and Primary key which uniquely identifies all other attributes

  • name, jurisdiction for a secondary relationship that can identify authorityid

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


PublicHealthAuthorityHelpline


Table Structure:


Functional Dependency Check


helplineno=>authorityid


  • helplineno is a candidate key and Primary key which uniquely identifies AuthorityID, because a number cannot be shared by multiple authorities in our assumption

  • authorityID has multiple numbers hence not unique to relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


PublicHealthWorker


Table Structure:


Functional Dependency Check


Workerid => officeid

Workerid => Title

Workerid => publicauthid


  • WorkerID is a candidate key and Primary key which uniquely identifies Title, OfficeID, publicauthID

  • OfficeID is a candidate key which uniquely identified the PK

  • PubliAuthID has multiple workers so not unique to relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


Assessment


Table Structure:


Functional Dependency Check


AssessId=>takerid

AssessId=>covidsuspected

AssessId=>timestamp

AssessId=>responsesheet

AssessId=>assesseetype

AssessId=>overseeingworkerid


  • WorkerID is a candidate key and Primary key which uniquely identifies all the attributes

  • Same TakerID is on multiple assessments so not unique to relationship

  • Timestamp, takerid and overseeingworkerid form a secondary relationship (non-primes to key)

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


HealthReportCheck


Table Structure:


Functional Dependency Check


ReportID=>userid

ReportID=>temperature

ReportID=>oxygenlevel

ReportID=>healthstatus

ReportID=>testresult

ReportID=>description

ReportID=>checkingworkerid

ReportID=>reportdate


  • ReportID is a candidate key and Primary key which uniquely identifies all the attributes

  • Same UserID is on multiple Reports so not unique to relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


Interactions


Table Structure:


Functional Dependency Check


InteractionID=>interactinguserid

InteractionID=>interactingplaceid

InteractionID=>visitid


  • InteractionID is a candidate key and Primary key which uniquely identifies all the attributes

  • Same InteractingUseID, VisitorID and InteractionPlaceID can exist on multiple interactions so not unique to relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


UserEvent


Table Structure:


Functional Dependency Check


eventid=>logbookid

eventid=>description

eventid=>eventstarttime

eventid=>eventendtime


  • EventID is a candidate key and Primary key which uniquely identifies logbookid, description, eventstarttime, eventendtime

  • Same set of start time, end time and logbookid can exist in case the database is really large so they are not unique to relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


Place


Table Structure:


Functional Dependency Check


placeid=>placename

placeid=>streetarea

placeid=>state

placeid=>city

placeid=>coountry

placeid=>latitude

placeid=>longitude


  • placeid is a candidate key and Primary key which uniquely identifies logbookid, description, eventstarttime, eventendtime

  • Same set of address and coordinates can exist in case for multiple persons sharing an address, so they are not unique to relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


PersonNotification


Table Structure:


Nid,PersonID


  • (Nid,PersonID) is the only candidate key and a primary key, no non-prime attribute exist, so no dependency

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


Notifications


Table Structure:


Functional Dependency Check


nid=>PHAauthorityid

nid=>timestamp

nid=>otherinformation


  • nid is a candidate key and Primary key which uniquely identifies PHAAuthorityID, timestamp, otherinformation

  • trivial attributes (phaauthorityid, timestamp, otherinformation) form a secondary relationship. (Non-prime to key)

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF


ContactLogHistory


Table Structure:


Functional Dependency Check


logid=>date

logid=>assignedworkerid


  • logid is a candidate key and Primary key which uniquely identifies date and assignedworker

  • AssignedWorkerID can exist on multiple logs, so it is not unique to this relationship

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF

16 Visits


Table Structure:


Functional Dependency Check


visitid=>visitinguserid

visitid=>visitplaceid


  • VisitID is a candidate key and Primary key which uniquely identifies VisitPlaceID and VisitingUserID

  • (VisitPlaceID,VisitingUserId) do not uniquely identify a visit because, there can be multiple visits, even on the same day

  • No Partial Dependency or Multivalued Attribute exist in this relationship

  • There is no non-trivial FD without a candidate key. Hence table is in BCNF



Section 8. Integrity Checking Trials & Proofs


Referential Integrity : Foreign Key Check

Test #1

Attempt to deleted a referenced record:

DELETE from AppUser where userid = 1499;



image



Response



Text Box: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (contacttracingdb.appuseremail, CONSTRAINT appuseremail_ibfk_1 FOREIGN KEY (UserID) REFERENCES appuser (UserID)) 0.0039 sec


Test#2

Attempt to insert an non-referenced record:

INSERT INTO PublicHealthWorker VALUES(1600,‘MisterA’,131245,13112);



img



Response



Text Box: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (contacttracingdb.publichealthworker, CONSTRAINT publichealthworker_ibfk_2 FOREIGN KEY (WorkerID) REFERENCES person (PersonID))


Domain Integrity : Alien Value Check


Test #1

Attempt to insert a non-allowed value:

INSERT INTO Assessment VALUES(3899,1600,0,‘2020-05-29 08:37:55’,‘a random description’,‘Friend’,1511)





Response



Text Box: Error Code: 1265. Data truncated for column ‘AssesseeType’ at row 1 0.00028 sec



Test#2

Attempt to INSERT a value outside bounds:

INSERT INTO Place VALUES(1002,‘Parua’,‘RODQ PLACE’,’EAST

BOSTON’,‘MA’,‘2128’,42.36443246,-731234568910);



img

Response



Text Box: Error Code: 1264. Out of range value for column ‘longitude’ at row 1 0.00026 sec


Entity Integrity and Key Constraints : Primary Key Null


Test #1

Attempt to INSERT a NULL value for PRIMARY KEY :

INSERT INTO AppUserEmail(UserID, EmailID) VALUES (1101,null);

img

Response



Text Box: Error Code: 1048. Column ‘EmailID’ cannot be null



Test#2

Attempt to INSERT a VALUE in AUTO_INCREMENT Primary key field: We can clearly see that the Primary Key was autogenerated when a NULL value was passed.



Text Box: INSERT INTO Place

VALUES(NULL,‘Aman’,‘Batra PLACE’,‘SOUTH BOSTON’,‘MA’,‘2128’,42.36467840,-72.03322720);

Select * FROM PLACE where placename=‘Aman’;



Img

Response



Text Box: 1 row(s) affected. 1001 Aman Batra PLACE SOUTH BOSTON MA 2128 42.36467840 -72.03322720



Test#3- Checking Uniqueness of Keys

Counting primary keys of Places

Text Box: SELECT placeid,COUNT(*) as total FROM place GROUP BY placeid HAVING total > 1;





Response

Text Box: 0 row(s) returned



Section 9 .Create Tables and Insert Values

Initiating the Connection With Database


library(RMySQL)
library(DBI)
library(RColorBrewer)
conn = dbConnect(MySQL(), userName="root", password="atcbtra123", dbname="ContactTracingDB", host="localhost",port = 3306)

Exxecuting the Create Tables Queries via R or SQL)

  • The Entire databata can be replicated by executing this db import file availableHere
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS Place (
  placeid INT NOT NULL AUTO_INCREMENT, 
  placename VARCHAR(255) NOT NULL, 
  streetarea VARCHAR(255) NOT NULL, 
  state VARCHAR(255) NOT NULL, 
  city VARCHAR(255) NOT NULL, 
  zip VARCHAR(255) NOT NULL,
  latitude decimal(10,8) signed NOT NULL,
  longitude decimal(11,8) signed NOT NULL,
  PRIMARY KEY (placeid)
);')
## <MySQLResult:0,0,0>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS Person (
  PersonID INT NOT NULL AUTO_INCREMENT, 
  firstName VARCHAR(255) NOT NULL, 
  lastName VARCHAR(255), 
  PRIMARY KEY (PersonID)
);')
## <MySQLResult:0,0,1>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS AppUser (
  UserID INT NOT NULL, 
  Birthdate date NOT NULL, 
  AddressID INT NOT NULL, 
  RecoveredFromCovid Boolean, 
  PRIMARY KEY (UserID), 
  FOREIGN KEY(UserID) REFERENCES Person(PersonID), 
  FOREIGN KEY (AddressID) REFERENCES Place(PlaceID)
);')
## <MySQLResult:0,0,2>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS PublicHealthAuthority (
  AuthorityID INT NOT NULL AUTO_INCREMENT, 
  AuthAddressID INT NOT NULL, 
  Name VARCHAR(255) NOT NULL, 
  Jurisdiction VARCHAR(255), 
  PRIMARY KEY (AuthorityID), 
  FOREIGN KEY (AuthAddressID) REFERENCES Place(PlaceId)
);')
## <MySQLResult:0,0,3>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS PublicHealthWorker (
  WorkerID INT NOT NULL, 
  Title VARCHAR(255) NOT NULL, 
  OfficeID VARCHAR(255) NOT NULL, 
  PublicAuthID INT NOT NULL,
  PRIMARY KEY (WorkerID),
  FOREIGN KEY (PublicAuthID) REFERENCES PublicHealthAuthority(AuthorityID),
  FOREIGN KEY (WorkerID) REFERENCES Person(PersonId)
);')
## <MySQLResult:0,0,4>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS ContactHistoryLog (
  LogID INT NOT NULL AUTO_INCREMENT, 
  Date datetime, 
  AssignedWorkerID INT NOT NULL, 
  PRIMARY KEY (LogID), 
  FOREIGN KEY (AssignedWorkerID) REFERENCES PublicHealthWorker(WorkerId)
);')
## <MySQLResult:0,0,5>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS UserEvents (
  EventID INT NOT NULL AUTO_INCREMENT, 
  EventStartTime Datetime NOT NULL, 
  EventEndTime Datetime NOT NULL,
  LogBookID INT NOT NULL,
  Description VARCHAR(1200), 
  PRIMARY KEY (EventID),
  FOREIGN KEY (LogBookID) REFERENCES ContactHistoryLog(LogID)
);')
## <MySQLResult:0,0,6>
dbSendQuery(conn,"CREATE TABLE IF NOT EXISTS HealthReportCheck(
  ReportID INT NOT NULL AUTO_INCREMENT, 
  UserID INT NOT NULL, 
  Temperature decimal(10, 3), 
  OxygenLevel decimal(10, 3), 
  HealthStatus ENUM ('sick','healthy') NOT NULL, 
  TestResult enum('Positive','Negative','Unclear') NOT NULL, 
  Description Text, 
  CheckingWorkerID INT NOT NULL, 
  ReportDate date,
  PRIMARY KEY (ReportID), 
  FOREIGN KEY (UserID) REFERENCES AppUser(UserID), 
  FOREIGN KEY(CheckingWorkerID) REFERENCES PublicHealthWorker(WorkerId)
);")
## <MySQLResult:0,0,7>
dbSendQuery(conn,"CREATE TABLE IF NOT EXISTS Assessment (
  AssessID INT NOT NULL AUTO_INCREMENT, 
  TakerID INT NOT NULL, 
  CovidSuspected boolean NOT NULL, 
  TimeStamp Datetime NOT NULL, 
  ResponseSheet VARCHAR(255), 
  AssesseeType enum('self','doctor','others') NOT NULL, 
  OverseeingWorkerID INT NOT NULL, 
  PRIMARY KEY (AssessID), 
  FOREIGN KEY (TakerID) REFERENCES AppUser(UserId), 
  FOREIGN KEY (OverseeingWorkerID) REFERENCES PublicHealthWorker(WorkerId)
);")
## <MySQLResult:0,0,8>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS AppUserEmail (
  UserID INT NOT NULL, 
  EmailID VARCHAR(255) NOT NULL, 
  PRIMARY KEY (EmailID), 
  FOREIGN KEY(UserID) REFERENCES AppUser(UserID)
);')
## <MySQLResult:0,0,9>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS PublicHealthAuthorityHelpline(
  HelplineNo VARCHAR(255) NOT NULL, 
  AuthorityID INT NOT NULL, 
  PRIMARY KEY(HelplineNo), 
  FOREIGN KEY(AuthorityID) REFERENCES PublicHealthAuthority(AuthorityID)
);')
## <MySQLResult:0,0,10>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS Interactions (
  InteractionID INT NOT NULL, 
  InteractingUserID INT NOT NULL, 
  VisitorID INT NOT NULL, 
  InteractionPlaceID INT NOT NULL, 
  PRIMARY KEY (InteractionID), 
  FOREIGN KEY (InteractionID) REFERENCES UserEvents(EventID), 
  FOREIGN KEY (InteractingUserID) REFERENCES AppUser(UserID), 
  FOREIGN KEY (VisitorID) REFERENCES Person(PersonID), 
  FOREIGN KEY (InteractionPlaceID) REFERENCES Place(PlaceId)
);')
## <MySQLResult:0,0,11>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS Visit (
  VisitID INT NOT NULL, 
  VisitPlaceID INT NOT NULL, 
  VisitingUserID INT NOT NULL, 
  PRIMARY KEY (VisitID), 
  FOREIGN KEY (VisitID) REFERENCES UserEvents(EventID), 
  FOREIGN KEY (VisitPlaceID) REFERENCES Place(PlaceID), 
  FOREIGN KEY(VisitingUserID) REFERENCES AppUser(UserID)
);')
## <MySQLResult:0,0,12>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS Notifications (
  Nid INT NOT NULL AUTO_INCREMENT, 
  PHAAuthorityID INT NOT NULL, 
  timeStamp datetime NOT NULL, 
  OtherInformation Text, 
  PRIMARY KEY (Nid), 
  FOREIGN KEY (PHAAuthorityID) REFERENCES PublicHealthAuthority(AuthorityId)
);')
## <MySQLResult:0,0,13>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS PersonNotification (
  Nid INT NOT NULL, 
  PersonID INT NOT NULL, 
  PRIMARY KEY (Nid, PersonID), 
  FOREIGN KEY(Nid) REFERENCES Notifications(Nid), 
  FOREIGN KEY(PersonID) REFERENCES Person(PersonID)
);')
## <MySQLResult:0,0,14>
dbSendQuery(conn,'CREATE TABLE IF NOT EXISTS PersonPhone(
  PersonID INT NOT NULL, 
  PhoneNo VARCHAR(20) NOT NULL, 
  PRIMARY KEY (PhoneNo), 
  FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);')
## <MySQLResult:0,0,15>

Output Screenshots

Screenshot 1

Table Creation

Screenshot 2

Table Creation

Screenshot 3

Table Creation

Screenshot 4

Table Creation



Inserting the values into the tables:

  • The Insert script can be downloaded from here Here
  • The Entire databata can be replicated by executing this db import file availableHere
# The INSERT data is too large for this operation through R, 
#please use file links provided above to load the data into schema

dbDisconnect(conn)
## Warning: Closing open result sets
## [1] TRUE

Screenshot 5

Table Insertion

Screenshot 6

Table Insertion

Section 10. Query Generation, Scripts & Outputs



## QUERY - 1 - A SIMPLE JOIN QUERY

Joining Tables: AppUser, Person, AppUserEmail

Goal : To find all the details of a user who tested positive in COVID-19 Drug Test

SQL: SELECT * FROM AppUser

INNER JOIN Person ON AppUser.UserID=Person.PersonID

INNER JOIN AppUserEmail on AppUser.UserID=AppUserEmail.UserID

INNER JOIN Place on AppUser.AddressID=Place.placeid

INNER JOIN HealthReportCheck on AppUser.UserId=HealthReportCheck.UserID

WHERE HealthReportCheck.TestResult=‘positive’;

Response 153 row(s) returned

RESULT SCREENSHOTS:

  • Divided into two parts

PART 1

PART 2

R-Notebook Execution

library(RMySQL)
library(DBI)
library(RColorBrewer)
conn = dbConnect(MySQL(), userName="root", password="atcbtra123", dbname="ContactTracingDB", host="localhost",port = 3306)
val1 <- dbFetch(dbSendQuery(conn,"SELECT * FROM AppUser
INNER JOIN Person ON AppUser.UserID=Person.PersonID
INNER JOIN AppUserEmail on AppUser.UserID=AppUserEmail.UserID
INNER JOIN Place on AppUser.AddressID=Place.placeid
INNER JOIN HealthReportCheck on AppUser.UserId=HealthReportCheck.UserID
WHERE HealthReportCheck.TestResult='positive';"))
## Warning in .local(conn, statement, ...): Decimal MySQL column 19 imported as
## numeric
## Warning in .local(conn, statement, ...): Decimal MySQL column 20 imported as
## numeric
## Warning in .local(conn, statement, ...): Decimal MySQL column 23 imported as
## numeric
## Warning in .local(conn, statement, ...): Decimal MySQL column 24 imported as
## numeric
val1


## QUERY 2 - A Subquery to count total number of possible cases of direct person to person transmission in Massachusetts state.

SQL:

SELECT count(*) as “Total probable cases of Direct Transmission via person to person interactions in Massachusetts”

FROM

(

SELECT i.InteractingUserID FROM Interactions i

INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID

INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID

INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID

INNER JOIN Place on i.InteractionPlaceID=place.placeid

WHERE r1.TestResult=‘POSITIVE’ and r2.TestResult in (‘NEGATIVE’,‘UNCLEAR’)

AND r1.ReportDate<r2.ReportDate

AND Place.State in (‘MA’)

) as derived;

1 row(s) returned

RESULT SCREENSHOTS:

R-Notebook Execution

val2 <- dbFetch(dbSendQuery(conn,"SELECT count(*) as  TotalProbableCasesViaPersonToPersonMassachusetts
FROM
 (SELECT i.InteractingUserID FROM Interactions i
INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID
INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID
INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID
INNER JOIN Place on  i.InteractionPlaceID=place.placeid
WHERE r1.TestResult='POSITIVE' and r2.TestResult in ('NEGATIVE','UNCLEAR')
AND r1.ReportDate<r2.ReportDate
AND Place.State in ('MA')
) as derived;" ))

val2


## QUERY 3 - A QUERY with a HAVING CLAUSE to return the details of visits made by people who tested positive, within 20 days(before and after) of getting the test reports. Where the visit lasted longer than 4 hours.

SQL:

SELECT a.UserID, p.firstName, s.PhoneNo, u.EventStartTime as “Time of Visit”,TIMEDIFF(u.EventEndTime,u.EventStartTime)as DurationOfVisit, m.placename,m.streetarea, m.city, m.longitude, m.latitude FROM visit v

INNER JOIN AppUser a on v.VisitingUserID=a.UserID

INNER JOIN Person p on a.userid=p.PersonID

INNER JOIN PersonPhone s on p.PersonID=s.PersonID

INNER JOIN Place m on v.VisitPlaceID=m.placeid

INNER JOIN UserEvents u on v.VisitID=u.EventID

INNER JOIN HealthReportCheck h on a.UserID=h.UserID

WHERE h.TestResult=‘POSITIVE’

AND datediff(h.ReportDate, u.EventStartTime)<20

AND datediff(h.ReportDate, u.EventStartTime)>-20

HAVING DurationOfVisit>‘04:00:00’

ORDER by DurationOfVisit

9 row(s) returned

RESULT SCREENSHOTS:

R-Notebook Execution

val3 <- dbFetch(dbSendQuery(conn,"SELECT a.UserID, p.firstName, s.PhoneNo, u.EventStartTime as TimeOFVisit,TIMEDIFF(u.EventEndTime,u.EventStartTime)as DurationOfVisit, m.placename,m.streetarea, m.city, m.longitude, m.latitude  FROM visit v
INNER JOIN AppUser a on v.VisitingUserID=a.UserID
INNER JOIN Person p on a.userid=p.PersonID
INNER JOIN PersonPhone s on p.PersonID=s.PersonID
INNER JOIN Place m on v.VisitPlaceID=m.placeid
INNER JOIN UserEvents u on v.VisitID=u.EventID
INNER JOIN HealthReportCheck h on a.UserID=h.UserID
WHERE h.TestResult='POSITIVE'
AND datediff(h.ReportDate, u.EventStartTime)<20 
AND datediff(h.ReportDate, u.EventStartTime)>-20
HAVING DurationOfVisit>'04:00:00'
ORDER by DurationOfVisit"))
## Warning in .local(conn, statement, ...): Decimal MySQL column 8 imported as
## numeric
## Warning in .local(conn, statement, ...): Decimal MySQL column 9 imported as
## numeric
val3


## QUERY 4 - A COMPLEX QUERY - A depth-2 search query to find out all interactions of persons, who came out positive/unclear in the covid-19 test, after their interacting with some person in the past.

Example Case:

  • John Doe meets Sabrina Chan, and Jian Yang.
  • John Doe was found positive, so Sabrina Chan and Jian Yang take covid tests.
  • Jian tests positive, while Sabrina tests negative, so we need to trace Jian’s interactions now.

SQL:

select o.FirstName as “Guy1 who found out he got exposed”, p.FirstName as “Guy2 who had met guy1”,

s.PhoneNo as “Phone number of Guy2”, u.eventstarttime as “Interaction Start Details” ,

u.eventendtime as “Interaction End Details” from interactions a, person p,

personphone s,userevents u, person o

WHERE a.InteractionID=u.eventid

AND p.personid = a.visitorid

AND s.PersonID=p.PersonId

AND a.Interactinguserid=o.personid

AND a.interactinguserid IN (

SELECT i.VisitorID FROM Interactions i

INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID

INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID

INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID

WHERE r1.TestResult=‘POSITIVE’ and r2.TestResult in (‘UNCLEAR’,‘POSITIVE’)

AND r1.ReportDate< r2.ReportDate

)

AND a.Interactinguserid NOT IN (

SELECT i.interactinguserid FROM Interactions i

INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID

INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID

INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID

WHERE r1.TestResult=‘POSITIVE’ and r2.TestResult in (‘UNCLEAR’,‘POSITIVE’)

AND r1.ReportDate< r2.ReportDate

)

37 row(s) returned

RESULT SCREENSHOTS

Results

Note: We don’t use the distinct keyword because we want to find out all interactions, even if there were more than one!

We can go till depth 5 with MySQL in a medium sized database, after depth-5 mysqld crashes within 30 minutes of wait.

R-Notebook Execution

val4 <- dbFetch(dbSendQuery(conn,"select o.FirstName as Guy1_who_found_out_he_got_exposed, p.FirstName as Guy2_who_had_met_guy1, s.PhoneNo as Phone_number_of_Guy2, u.eventstarttime as Interaction_Start_Details , u.eventendtime as Interaction_End_Details from interactions a, person p, personphone s,userevents u, person o
WHERE a.InteractionID=u.eventid
AND p.personid = a.visitorid
AND s.PersonID=p.PersonId
AND a.Interactinguserid=o.personid

AND a.interactinguserid IN (
SELECT  i.VisitorID FROM Interactions i
INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID
INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID
INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID
WHERE r1.TestResult='POSITIVE' and r2.TestResult in ('UNCLEAR','POSITIVE')
AND  r1.ReportDate< r2.ReportDate
)

AND a.Interactinguserid NOT IN (
SELECT  i.interactinguserid FROM Interactions i
INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID
INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID
INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID
WHERE r1.TestResult='POSITIVE' and r2.TestResult in ('UNCLEAR','POSITIVE')
AND  r1.ReportDate< r2.ReportDate
)   "))

val4


## QUERY 5 - A Query of Choice

Details of interactions of persons who tested COVID positive(Depth 1 -> Guy 0 meets Guy 1)

SQL:

SELECT i.interactingUserId as UniqueID, guy1.firstName as “Meeting Person 1”,

r1.TestResult as “First Guy’s Covid Report”,i.VisitorID as UniqueID,

guy2.firstName as “Meeting Person 2”, r2.TestResult as “Second Guy’s Covid Report”,

UserEvents.EventStartTime as “DateTime of Meeting”,

UserEvents.Description as “Details of Meeting”

FROM Interactions i

INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID

INNER JOIN Person guy1 on i.InteractingUserID=guy1.PersonID

INNER JOIN Person guy2 on i.VisitorID=guy2.PersonID

INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID

INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID

WHERE r1.TestResult=‘POSITIVE’

228 row(s) returned

RESULTS SCREENSHOTS:

Results

R-Notebook Execution

val5 <- dbFetch(dbSendQuery(conn,"SELECT i.interactingUserId as UniqueID,
guy1.firstName as Meeting_Person_1, r1.TestResult as First_Guys_Covid_Report,
i.VisitorID as UniqueID, guy2.firstName as Meeting_Person_2, 
r2.TestResult as Second_Guys_CovidReport, 
UserEvents.EventStartTime as DateTime_of_Meeting, UserEvents.Description as Details_of_Meeting
 FROM Interactions i
INNER JOIN UserEvents on i.InteractionID=UserEvents.EventID
INNER JOIN Person guy1 on i.InteractingUserID=guy1.PersonID
INNER JOIN Person guy2 on i.VisitorID=guy2.PersonID
INNER JOIN HealthReportCheck r1 on i.InteractingUserID=r1.UserID
INNER JOIN HealthReportCheck r2 on i.VisitorID=r2.UserID
WHERE r1.TestResult='POSITIVE'"))

val5


## QUERY 6 - EXTRA QUERY

A QUERY with HAVING clause to count the no of people per city above 60 years in age, who took the Covid-19 Assessment survey, and suspected an infection, Only listing cities with more than 10 distinct suspects.

SQL:

SELECT distinct Place.city, count(distinct Assessment.TakerID)

AS “Total Covid Suspects around Boston region, with age greater than 50” FROM Place

INNER JOIN AppUser on Place.placeid=AppUser.addressid

INNER JOIN Assessment on AppUser.UserID=Assessment.TakerID

WHERE Assessment.CovidSuspected=1 AND AppUser.Birthdate<‘1960-01-01’

Group By Place.City HAVING count(distinct Assessment.TakerID)>10;

RESULT SCREENSHOTS:

R-Notebook Execution

val6 <- dbFetch(dbSendQuery(conn,"SELECT distinct Place.city, 
count(distinct Assessment.TakerID) as TotalCovidSuspectsInBostonregionAgeGreaterThan50 
FROM Place
INNER JOIN AppUser on Place.placeid=AppUser.addressid
INNER JOIN Assessment on AppUser.UserID=Assessment.TakerID
WHERE Assessment.CovidSuspected=1 AND AppUser.Birthdate<'1960-01-01'
Group By Place.City HAVING count(distinct Assessment.TakerID)>10;"))

val6


## QUERY 7 - EXTRA QUERY** Exposure events and places and Massachusetts. This query backtracks the visit log of a COVID-19 positive persons and returns exact places where they visited and spent some time.

SELECT m.placename,streetarea, m.city,u.EventStartTime as ExposureStart, u.EventEndTime as ExposureEnd FROM visit v INNER JOIN AppUser a on v.VisitingUserID=a.UserID INNER JOIN Person p on a.userid=p.PersonID INNER JOIN PersonPhone s on p.PersonID=s.PersonID INNER JOIN Place m on v.VisitPlaceID=m.placeid INNER JOIN UserEvents u on v.VisitID=u.EventID INNER JOIN HealthReportCheck h on a.UserID=h.UserID WHERE h.TestResult=‘POSITIVE’ AND M.state=‘MA’ AND datediff(h.ReportDate, u.EventStartTime)<20 AND datediff(h.ReportDate, u.EventStartTime)>-20

10 row(s) returned

RESULT SCREENSHOTS:

R-Notebook Execution

val7 <- dbFetch(dbSendQuery(conn,"SELECT m.placename,streetarea, m.city,u.EventStartTime as ExposureStart, u.EventEndTime as ExposureEnd FROM visit v
INNER JOIN AppUser a on v.VisitingUserID=a.UserID
INNER JOIN Person p on a.userid=p.PersonID
INNER JOIN PersonPhone s on p.PersonID=s.PersonID
INNER JOIN Place m on v.VisitPlaceID=m.placeid
INNER JOIN UserEvents u on v.VisitID=u.EventID
INNER JOIN HealthReportCheck h on a.UserID=h.UserID
WHERE h.TestResult='POSITIVE'
AND m.state='MA'
AND datediff(h.ReportDate, u.EventStartTime)<20 
AND datediff(h.ReportDate, u.EventStartTime)>-20
"))

val7
dbDisconnect(conn)
## Warning: Closing open result sets
## [1] TRUE

# Section 11. Plotting MySQL-Integrated Graphs in R**
  • Initializing connection to the database:
library(RMySQL)
library(DBI)
library(RColorBrewer)
conn = dbConnect(MySQL(), userName="root", password="atcbtra123", dbname="ContactTracingDB", host="localhost",port = 3306)
  • Getting the required values from database into our variable val12
#Playing around with SELECT CASE
val12 = dbFetch(dbSendQuery(conn, 'SELECT UserId, TestResult, HealthStatus,
    CASE 
    WHEN TestResult ="Positive" THEN "The user is COVID-19 Positive."
    WHEN TestResult = "Unclear" AND HealthStatus = "Sick" THEN "The user may be COVID-19 Positive"
    WHEN TestResult = "Negative" AND HealthStatus = "Sick" THEN "The user is COVID-19 Negative but suffers from some ailment"
    WHEN TestResult = "Negative" AND HealthStatus = "Healthy" THEN "The user is COVID-19 Free And Healthy"
    ELSE "Patient Status Unknown"
END
FROM HealthReportCheck;'))
val12
  • Plotting the acquired values
# A plot of total tests done and their results

library(ggplot2)
library(ggpubr)
theme_set(theme_pubr())

ggplot(val12,aes(TestResult)) +
  geom_bar(fill = "#0073C2FF") +
  theme_pubclean()

ggplot(val12,aes(HealthStatus)) +
  geom_bar(fill = "#0073C2FF") +
  theme_pubclean()

Playing around with plots

dbClearResult(dbListResults(conn)[[1]])
## [1] TRUE
#Playing around with Plots
res<-dbSendQuery(conn, 'SELECT count(UserId),RecoveredFromCovid
FROM AppUser
GROUP BY RecoveredFromCovid;')
val13 <- fetch(res, n = -1) 
val13
  • Output piechart
slices <- val13
lbls <- c("Developed Immunity", "Still Fighting Covid")
pie(table(val13),labels = lbls, col=rainbow(length(lbls)),
   main="Pie Chart of COVID-19 Recovery")

dbDisconnect(conn)
## Warning: Closing open result sets
## [1] TRUE



## GPS Plot in R Studio

library(RMySQL)
library(DBI)
library(RColorBrewer)
conn = dbConnect(MySQL(), userName="root", password="atcbtra123", dbname="ContactTracingDB", host="localhost",port = 3306)
# A plot of COVID-19 positive cases in Boston and Dartmouth

res<-dbSendQuery(conn,"SELECT p.latitude as Longitude,p.longitude as Latitude FROM place p,AppUser,HealthReportCheck

WHERE p.placeid=AppUser.AddressID

AND AppUser.UserID=HealthReportCheck.UserID

AND HealthReportCheck.TestResult='POSITIVE';")
## Warning in .local(conn, statement, ...): Decimal MySQL column 0 imported as
## numeric
## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric
ls1<-fetch(res, n=-1)

ls1
library(leaflet)
leaflet() %>%
  addTiles() %>%
  addMarkers(as.vector(ls1[1:134,"Latitude"]),as.vector(ls1[1:134,"Longitude"]), icon = list(iconUrl = 'https://amanbatra.in/static/img3/covid.png',

iconSize = c(25, 25)

))
## Warning in validateCoords(lng, lat, funcName): Data contains 1 rows with either
## missing or invalid lat/lon values and will be ignored


Screenshots of GPS Plots

“GPS PLOT FOR Number of Positive Cases in Boston and Dartmouth, GPS is NOT Supported with Markdown Knitting, so attaching a screenshot of the same”

“Output-Interaction map”